PostgreSQL 14からJSONデータを辞書風にアクセスできるようになりました

PostgreSQL 14からJSONBデータをサブスクリプトに対応しました。 JSON型のみ対応し、JBON型では対応していことに注意。

#PostgreSQL

#JSON

quiver

2021.10.28

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

PostgreSQLは9.2からJSONデータに対応しています。スキーマを意識せずにデータを格納できるため、重宝する一方で、参照・更新時のアクセス方法が独特でとっつきにくいところもありました。

PostgreSQL 14から直感的なサブスクリプト(辞書/マップ)風のアクセスにも対応したため、この方法を紹介します。

ポイント

PostgreSQL 14から連結配列風のサブスクリプトに対応。
JSONB型のみ対応。JSON型は未対応
戻り値はJSONB型なので、適宜型をあわせる

やってみた

前提

PostgreSQL 14 を起動します。

test=> select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 14rc1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)

今回は AWS RDS PostgreSQL 14のパブリックプレビューを利用しました。

準備:検証データの投入

JSONデータを扱えるよう、JSONB型のカラムを含んだテーブルを作成します。

JSON型 は 14 からのサブスクリプト対応していないので、ご注意ください。

CREATE TABLE orders (
	id serial,
	info jsonb
);


INSERT INTO orders (info)
VALUES('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'),
      ('{ "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}'),
      ('{ "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}'),
      ('{ "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}');


SELECT * FROM orders;
 id |                                  info
----+-------------------------------------------------------------------------
  1 | { "customer": "John Doe", "items": {"product": "Beer","qty": 6}}
  2 | { "customer": "Lily Bush", "items": {"product": "Diaper","qty": 24}}
  3 | { "customer": "Josh William", "items": {"product": "Toy Car","qty": 1}}
  4 | { "customer": "Mary Clark", "items": {"product": "Toy Train","qty": 2}}
(4 rows)

JSONデータを参照

JSON演算子方式

従来の JSON 演算子では、 ->, ->> などで属性にアクセスし、 -> の場合は、JSONB 型、 ->> の場合は TEXT 型がかえってきました。

SELECT info -> 'items',
       (info -> 'items' -> 'qty')::integer + 3
FROM orders
WHERE info ->> 'customer'  = 'John Doe';

           ?column?            | ?column?
-------------------------------+----------
 {"qty": 6, "product": "Beer"} |        9
(1 row)

サブスクリプト方式

新しいサブスクリプトアクセスでは JSONB 型がかえってきます。 ::integer や '"John Doe"' で型をあわせています。

SELECT info['items'],
       info['items']['qty']::integer + 3
FROM orders
WHERE info['customer']  = '"John Doe"';

-------------------------------+----------
 {"qty": 6, "product": "Beer"} |        9
(1 row)

JSONデータを更新

サブスクリプトは更新にも使えます。

JSON演算子方式

JSON 演算子を使って属性を変更する場合、冗長・独特な構文を使う必要がありました。

UPDATE  orders
SET info = jsonb_set(info::jsonb, '{items, product}', '"Wine"')
WHERE id = 1;

難易度の高さから、Stackoverflow でも、人気の質問の一つでした。

How do I modify fields inside the new PostgreSQL JSON datatype? - Stack Overflow

サブスクリプト方式

サブスクリプトを使うと、参照と同等の構文で更新できます。

UPDATE orders
SET info['items']['product'] = '"Wine"'
WHERE id = 1;

ソースコードリーディングの観点では、サブスクリプト構文のほうが、何をやっているのか理解しやすいのではないでしょうか?

JSON 型でサブスクリプト構文を利用するとエラーになる

JSON型のカラムではサブスクリプトを利用できないことを確認します。

test=> \d orders2
                            Table "public.orders2"
 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+-------------------------------------
 id     | integer |           | not null | nextval('orders2_id_seq'::regclass)
 info   | json    |           |          |

test=> select info['customer'] from orders2;
ERROR:  cannot subscript type json because it does not support subscripting
LINE 1: select info['customer'] from orders2;